package com.tansun.easycare.rule.datamodel.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.druid.util.StringUtils;
import com.tansun.easycare.core.service.impl.CrudServiceImpl;
import com.tansun.easycare.rule.datamodel.entity.RuleDataEntity;
import com.tansun.easycare.rule.datamodel.entity.RuleDataparams;
import com.tansun.easycare.rule.datamodel.entity.RuleDataset;
import com.tansun.easycare.rule.datamodel.service.DBtoDataModelService;
import com.tansun.easycare.rule.datamodel.service.DataModelService;
import com.tansun.easycare.rule.datamodel.service.RuleDataparamsService;
import com.tansun.easycare.rule.datasource.dao.RuleDatasheetDao;
import com.tansun.easycare.rule.datasource.entity.RuleDatasheet;
import com.tansun.easycare.rule.datasource.entity.RuleDatasheetColumn;
import com.tansun.easycare.rule.datasource.service.RuleDatasourceService;
import com.tansun.easycare.rule.exception.BizException;
import com.tansun.easycare.rule.front.entity.RuleDatasource;

/**
 * 导入表ServiceImpl
 *
 * @author ypx
 * @version 2018-08-09
 */
@Service("dbtoDataModelService")
@Transactional(readOnly = true)
public class DBtoDataModelServiceImpl extends CrudServiceImpl<RuleDatasheetDao, RuleDatasheet> implements DBtoDataModelService {
	private final Logger log = LoggerFactory.getLogger(this.getClass());
	@Autowired
	private RuleDatasourceService datasourceService;
	
	@Autowired 
	private RuleDataparamsService ruleDataparamsService;
	
	@Autowired 
	private DataModelService dataModelService;
	
	@Override
	public List<RuleDatasheet> findDbTable(RuleDatasource ruleDatasource) throws Exception {
		String sql = null;
		if("oracle".equalsIgnoreCase(ruleDatasource.getDsType())){
			Class.forName("oracle.jdbc.driver.OracleDriver");  //加载驱动类
			sql = "select table_name,comments from user_tab_comments";
		}else if("mysql".equalsIgnoreCase(ruleDatasource.getDsType())){
			Class.forName("com.mysql.jdbc.Driver");
			sql = "select TABLE_NAME,TABLE_COMMENT from information_schema.TABLES where table_schema = (select database())";
		}else{
			log.warn("不支持的数据库类型:"+ruleDatasource.getDsType());
			return null;
		}//使用DriverManager获得连接对象，其中url每个数据库不同 
		try(Connection connection = DriverManager.getConnection(ruleDatasource.getDsUrl(), ruleDatasource.getDsUsername(), ruleDatasource.getDsPwd());){
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();//执行查询，返回结果集 
			List<RuleDatasheet> list = new ArrayList<RuleDatasheet>();
			while(rs.next()){
				RuleDatasheet e = new RuleDatasheet();
				e.setName(rs.getString(1));
				e.setComments(rs.getString(2));
				list.add(e);
			}
			return list;
		}	
	}

	@Override
	public List<RuleDatasheetColumn> findTableCols(RuleDatasheet entity) throws Exception {
		if(entity == null || StringUtils.isEmpty(entity.getDatasourceId())) return new ArrayList<>();
		RuleDatasource datasource = datasourceService.get(entity.getDatasourceId());
		String sql = null;
		if("oracle".equalsIgnoreCase(datasource.getDsType())){
			Class.forName("oracle.jdbc.driver.OracleDriver");
			sql = "SELECT \n" + 
					"			a.table_name,\n" + 
					"			b.column_name column_name,\n" + 
					"      b.data_type data_type,\n" + 
					"      a.comments comments\n" + 
					"FROM user_col_comments a\n" + 
					"    ,user_tab_columns b\n" + 
					"WHERE  a.table_name = ?\n" + 
					"and a.table_name = b.table_name\n" + 
					"and a.column_name = b.column_name";
		}else if("mysql".equalsIgnoreCase(datasource.getDsType())){
			Class.forName("com.mysql.jdbc.Driver");
			sql = "SELECT \n" + 
					"  TABLE_NAME,\n" + 
					"  COLUMN_NAME,\n" + 
					"  COLUMN_TYPE,\n" + 
					"  COLUMN_COMMENT\n" + 
					"FROM\n" + 
					"  INFORMATION_SCHEMA.COLUMNS \n" + 
					"WHERE TABLE_SCHEMA = (select database()) and TABLE_NAME =?";
		}else{
			log.warn("不支持的数据库类型:"+datasource.getDsType());
			return null;
		}
		try(Connection connection = DriverManager.getConnection(datasource.getDsUrl(), datasource.getDsUsername(), datasource.getDsPwd());){
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setString(1, entity.getName());
			ResultSet rs = stmt.executeQuery();
			List<RuleDatasheetColumn> list = new ArrayList<RuleDatasheetColumn>();
			while(rs.next()){
				RuleDatasheetColumn e = new RuleDatasheetColumn();
				e.setName(rs.getString(2));
				e.setColType(rs.getString(3));
				e.setComments(rs.getString(4));
				list.add(e);
			}
			return list;
		}
	}

	

	@Override
	public void saveOrUpdate(RuleDataEntity ruleDataEntity) {
		RuleDataset ds = ruleDataEntity.getRuleDataset();
		dataModelService.save(ds);
		
		if(ds.getId()!=null) {
			RuleDataparams condition =null;
			for (RuleDataparams p : ruleDataEntity.getRuleDataparams()) {
				condition =new RuleDataparams();
				condition.setProjectcode(p.getProjectcode());
				condition.setDatasetId(ds.getId());
				condition.setName(p.getName());
				List<RuleDataparams> list = ruleDataparamsService.findList(condition);
				if (list.size()>0) {
					throw new BizException("",condition.getName()+"已经存在");
				}
				p.setDatasetId(ds.getId());
			}
		}
		
		ruleDataparamsService.insertBatch(ruleDataEntity.getRuleDataparams());
		

	}

	@Override
	public List<LinkedHashMap<String, Object>> getTableDataPage(String datasourceId, String tableName, int pageNo,
			int pageSize) throws Exception {
		RuleDatasource datasource = datasourceService.get(datasourceId);
		RuleDatasheet condition = new RuleDatasheet();
		condition.setDatasourceId(datasourceId);
		condition.setName(tableName);
		List<RuleDatasheetColumn> columns = findTableCols(condition);
		String sql2 = "select ";
		for (RuleDatasheetColumn ruleDatasheetColumn : columns) {
			sql2 += ruleDatasheetColumn.getName() + ",";
		}
		sql2 = sql2.substring(0,sql2.length()-1);
		sql2 += " from "+tableName;
		int firstResult = (pageNo - 1) * pageSize;
		sql2 = getLimitString(sql2, firstResult, Integer.toString(firstResult), Integer.toString(pageSize),datasource.getDsType());
		
		List<LinkedHashMap<String,Object>> resultList = new ArrayList<>();
		try(Connection connection = DriverManager.getConnection(datasource.getDsUrl(), datasource.getDsUsername(), datasource.getDsPwd());){
			PreparedStatement stmt2 = connection.prepareStatement(sql2);
			ResultSet rs = stmt2.executeQuery();
			while(rs.next()){
				LinkedHashMap<String,Object> rsMap = new LinkedHashMap<>();
				for (RuleDatasheetColumn rdc : columns) {
					rsMap.put(rdc.getName(), rs.getString(rdc.getName()));
				}
				resultList.add(rsMap);
			}
		}
		return resultList;
	}

	public String getLimitString(String sql, int offset, String offsetPlaceholder, String limitPlaceholder,String type) {
		sql = sql.trim();
		if(type.equalsIgnoreCase("mysql")) {
	        StringBuilder stringBuilder = new StringBuilder(sql);
	        stringBuilder.append(" limit ");
	        if (offset > 0) {
	            stringBuilder.append(offsetPlaceholder).append(",").append(limitPlaceholder);
	        } else {
	            stringBuilder.append(limitPlaceholder);
	        }
	        return stringBuilder.toString();
		}else {
			 boolean isForUpdate = false;
	        if (sql.toLowerCase().endsWith(" for update")) {
	            sql = sql.substring(0, sql.length() - 11);
	            isForUpdate = true;
	        }
	        StringBuilder pagingSelect = new StringBuilder(sql.length() + 100);

	        if (offset > 0) {
				pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
			} else {
				pagingSelect.append("select * from ( ");
			}
			pagingSelect.append(sql);
			if (offset > 0) {
				String endString = offsetPlaceholder + "+" + limitPlaceholder;
				pagingSelect.append(" ) row_ where rownum <= "+endString+") where rownum_ > ").append(offsetPlaceholder);
			} else {
				pagingSelect.append(" ) where rownum <= "+limitPlaceholder);
			}

	        if (isForUpdate) {
	            pagingSelect.append(" for update");
	        }

	        return pagingSelect.toString();
		}
    }

	
	
}

	



